package com.example.demo.repository;

import com.example.demo.entity.Location.LocationDTO;
import com.example.demo.entity.ProductBillDetail;
import jakarta.transaction.Transactional;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Page;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;

@Repository
public interface ProductBillDetailRepository extends JpaRepository<ProductBillDetail, Long> {

    @Query(value = "SELECT WA_produce_bill_master.plan_code, " +
            "A.bill_no, 0 as checked, A.goods_code, V_goods.goods_name, " +
            "V_goods.design_number_ver_spec, V_goods.unit_code, A.working_area_code, A.working_center_code, " +
            "A.wp_no, (SELECT TOP 1 wp_no FROM WA_produce_bill_detail_wp WHERE bill_no = A.bill_no ORDER BY wp_no DESC) as wp_no_last, " +
            "A.wp_code, A.plan_qty, A.id, A.workgroup_code, A.wp_id, p_wp_batch_stock.in_process_qty, " +
            "A.in_qty, A.repair_qty, A.complete_qty, V_plan_bill_list.goods_name, V_plan_bill_list.design_number_ver_spec, " +
            "V_plan_bill_list.client_name, V_plan_bill_list.client_goods_code, V_plan_bill_list.order_no, V_plan_bill_list.client_order_no, " +
            "(A.plan_qty - A.in_qty + A.repair_qty) as not_in_qty, A.parm_must_complete_work, " +
            "ISNULL(WA_produce_bill_master.combination_bill_no,'') AS combination_bill_no, " +
            "ISNULL(WA_produce_bill_detail_combination_id.parent_id,0) AS combination_wp_id, " +
            "WA_produce_bill_master.remark " +
            "FROM WA_produce_bill_detail_wp A " +
            "INNER JOIN WA_produce_bill_master ON A.bill_no = WA_produce_bill_master.bill_no " +
            "LEFT OUTER JOIN V_goods ON A.goods_code = V_goods.goods_code " +
            "LEFT OUTER JOIN p_wp_batch_stock ON A.bill_no = p_wp_batch_stock.product_bill_no " +
            "AND A.wp_id = p_wp_batch_stock.wp_id AND A.goods_code = p_wp_batch_stock.goods_code " +
            "LEFT OUTER JOIN V_plan_bill_list ON WA_produce_bill_master.plan_code = V_plan_bill_list.plan_code " +
            "AND WA_produce_bill_master.product_code = V_plan_bill_list.goods_code " +
            "LEFT OUTER JOIN WA_produce_bill_detail_combination_id ON A.id = WA_produce_bill_detail_combination_id.child_id " +
            "WHERE WA_produce_bill_master.bill_type <> '车间任务组合单'",
            countQuery = "SELECT COUNT(1) FROM WA_produce_bill_detail_wp A " +
                    "INNER JOIN WA_produce_bill_master ON A.bill_no = WA_produce_bill_master.bill_no " +
                    "LEFT OUTER JOIN V_goods ON A.goods_code = V_goods.goods_code " +
                    "LEFT OUTER JOIN p_wp_batch_stock ON A.bill_no = p_wp_batch_stock.product_bill_no " +
                    "AND A.wp_id = p_wp_batch_stock.wp_id AND A.goods_code = p_wp_batch_stock.goods_code " +
                    "LEFT OUTER JOIN V_plan_bill_list ON WA_produce_bill_master.plan_code = V_plan_bill_list.plan_code " +
                    "AND WA_produce_bill_master.product_code = V_plan_bill_list.goods_code " +
                    "LEFT OUTER JOIN WA_produce_bill_detail_combination_id ON A.id = WA_produce_bill_detail_combination_id.child_id " +
                    "WHERE WA_produce_bill_master.bill_type <> '车间任务组合单'",
            nativeQuery = true
    )
    Page<Object[]> getProductBillDetail(Pageable pageable);

    @Query(value = "SELECT TOP 1 id FROM WA_produce_bill_detail_wp WITH(NOLOCK) WHERE bill_no = :billNo AND wp_size_selected = 'n' ORDER BY wp_no DESC", nativeQuery = true)
    Long getID(String billNo);

    @Query(value = "SELECT WA_produce_bill_master.plan_code, " +
            "A.bill_no, 0 as checked, A.goods_code, V_goods.goods_name, " +
            "V_goods.design_number_ver_spec, V_goods.unit_code, A.working_area_code, A.working_center_code, " +
            "A.wp_no, (SELECT TOP 1 wp_no FROM WA_produce_bill_detail_wp WHERE bill_no = A.bill_no ORDER BY wp_no DESC) as wp_no_last, " +
            "A.wp_code, A.plan_qty, A.id, A.workgroup_code, A.wp_id, p_wp_batch_stock.in_process_qty, " +
            "A.in_qty, A.repair_qty, A.complete_qty, V_plan_bill_list.goods_name as goods_name2, V_plan_bill_list.design_number_ver_spec as design_number_ver_spec2, " +
            "V_plan_bill_list.client_name, V_plan_bill_list.client_goods_code, V_plan_bill_list.order_no, V_plan_bill_list.client_order_no, " +
            "(A.plan_qty - A.in_qty + A.repair_qty) as not_in_qty, A.parm_must_complete_work, " +
            "ISNULL(WA_produce_bill_master.combination_bill_no,'') AS combination_bill_no, " +
            "ISNULL(WA_produce_bill_detail_combination_id.parent_id,0) AS combination_wp_id, " +
            "WA_produce_bill_master.remark " +
            "FROM WA_produce_bill_detail_wp A " +
            "INNER JOIN WA_produce_bill_master ON A.bill_no = WA_produce_bill_master.bill_no " +
            "LEFT OUTER JOIN V_goods ON A.goods_code = V_goods.goods_code " +
            "LEFT OUTER JOIN p_wp_batch_stock ON A.bill_no = p_wp_batch_stock.product_bill_no " +
            "AND A.wp_id = p_wp_batch_stock.wp_id AND A.goods_code = p_wp_batch_stock.goods_code " +
            "LEFT OUTER JOIN V_plan_bill_list ON WA_produce_bill_master.plan_code = V_plan_bill_list.plan_code " +
            "AND WA_produce_bill_master.product_code = V_plan_bill_list.goods_code " +
            "LEFT OUTER JOIN WA_produce_bill_detail_combination_id ON A.id = WA_produce_bill_detail_combination_id.child_id " +
            "WHERE WA_produce_bill_master.bill_type <> '车间任务组合单'" +
            "and (A.id = :ID or WA_produce_bill_detail_combination_id.parent_id = :ID)", nativeQuery = true)
    List<ProductBillDetail> getDetail(Long ID);

    @Modifying
    @Transactional
    @Query(value = "insert into WA_produce_wp_in_repair_detail(bill_no, product_bill_no, wp_id, goods_code, remark, qty) values(:billNo, :productBillNo, :wpID, :goodsCode, :remark, :qty)", nativeQuery = true)
    void addRepairDetail(String billNo, String productBillNo, String wpID, String goodsCode, String remark, BigDecimal qty);

    @Modifying
    @Transactional
    @Query(value = "update WA_produce_wp_in_repair_detail set bill_no = :billNo, product_bill_no = :productBillNo, goods_code = :goodsCode where wp_id = :wpID", nativeQuery = true)
    void updateRepairDetail( String billNo, String productBillNo, String wpID, String goodsCode);

    @Query(value = "select top 1 id from WA_produce_bill_detail_wp where bill_no = :productBillNo order by wp_no desc", nativeQuery = true)
    String getWpID(String productBillNo);

    @Query(value = "select bill_no from WA_produce_bill_detail_wp where id = :wpID", nativeQuery = true)
    String getBillNo(String wpID);

    @Query(value = "select billStatus from ProductDetail where billNo = :billNo")
    String getStatus(String billNo);

    @Query(value = "select goods_kind from b_goods where goods_code = :goodsCode", nativeQuery = true)
    String getGoodsKindCode(String goodsCode);

    @Query(value = "SELECT goods_kind_power FROM b_user_goods_kind_power Where user_id = :userID", nativeQuery = true)
    String getGoodsKindPower(String userID);

    @Query(value = "select p.*,b.working_center_code,b.wa_location_name as location_name, w.working_area_name from p_wa_location_stock p, b_wa_location b, b_working_area w where p.goods_code = :goodsCode and b.wa_location_code = p.location_code and w.working_area_code = p.working_area_code", nativeQuery = true)
    List<LocationDTO> getLocation(String goodsCode);
}